<?php

/**
 * @version		$Id: hello.php 15 2009-11-02 18:37:15Z chdemko $
 * @package		Joomla16.Tutorials
 * @subpackage	Components
 * @copyright	Copyright (C) 2005 - 2010 Open Source Matters, Inc. All rights reserved.
 * @author		Christophe Demko
 * @link		http://joomlacode.org/gf/project/helloworld_1_6/
 * @license		License GNU General Public License version 2 or later
 */

// No direct access to this file
defined('_JEXEC') or die('Restricted access');

// import Joomla modelitem library
jimport('joomla.application.component.modelitem');

/**
 * HelloWorld Model
 */
class bgModelbg extends JModel
{
	var $_total = null;
    var $_pagination = null;
    
    public function __construct(){
    	parent::__construct();
 
        $mainframe = JFactory::getApplication();
 
        // Get pagination request variables
        $limit = $mainframe->getUserStateFromRequest('com_certification.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
        $limitstart = JRequest::getVar('limitstart', 0, '', 'int');
 
        // In case limit has been changed, adjust it
        $limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);
 
        $this->setState('limit', $limit);
        $this->setState('limitstart', $limitstart);
    }
    function load($cid){
        $table = & JTable::getInstance('bg','bgTable');
        $table->load($cid);
        return $table;
    }
    
    public function savetemp($data, $addNew = false){
    	$table = & JTable::getInstance('bg','bgTable');
        $table->bind($data);
        if($table->store(false, $addNew)){
            
            return true;
        }
        
        return false;
    }
    
    public function remove($cid){
        $cid = implode(',', $cid);
    	$query = "DELETE FROM coli WHERE COLI_CODE IN($cid)";
        $this->_db->setQuery($query);
        if($this->_db->query()){
            return true;
        }else{
            return false;
        }
    }
    //Check ID exist
    public function checkExistID($cid){
        $qr = "SELECT COLI_CODE FROM coli WHERE COLI_CODE = $cid";
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
        if(!empty($result)){ 
            return true;
        }
        return false;
    }
    //Get all list Certification
	function getAllCertification($list = null){

       $qr = "SELECT DISTINCT COLI_CODE, COLI_COCH_CODE, COCH_NAME, OCBA_NAME, COCM_NAME,COLI_OCBA_CODE,OCBA_NAME,COCH_OCBA_CODE
                FROM coli
                LEFT JOIN coch ON COLI_COCH_CODE = COCH_CODE
                LEFT JOIN ocba ON COLI_OCBA_CODE = OCBA_CODE
                LEFT JOIN cocm ON COLI_COCM_CODE = COCM_CODE";
                
        
       $where = array();
       //check user group
       $aut=new bgAuthorize();
      // check  user login
       $group=$aut->getUserGroup();
       $u=JFactory::getUser();
       $u_ocba= $u->USEM_OCBA_CODE;
       if($group==11)
       {
            $where[] = "COLI_OCBA_CODE = '$u_ocba'";
       }
       //search       
       if(!empty($list['filter_search'])){
            
            $where[] = "(COLI_COCH_CODE  = '$list[filter_search]' OR COCH_NAME LIKE '%$list[filter_search]%')";
       }
       
       if(isset($list['user_id'])){
            $where[] = "COLI_USER_CREATE = $list[user_id]";
       }
       
       if($where){
           $where = ' WHERE '. implode(' AND ', $where);
       }else{
           $where = null;
       }
        
       // get total number record
       $this->_total = $this->getTotal($where);
         
       $qr .= $where;
           
       // order by
       $qr .= " ORDER BY COLI_CODE DESC"; 
       
       // pagination
       $qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');
          
       //echo $qr;
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
	}
    
    function getTotal($where = null){
        // Load the content if it doesn't already exist
        if ($this->_total === null) {
            
            $query = "SELECT count(DISTINCT COLI_CODE)
                        FROM coli
                        LEFT JOIN coch ON COLI_COCH_CODE = COCH_CODE
                        LEFT JOIN ocba ON COLI_OCBA_CODE = OCBA_CODE
                        LEFT JOIN cocm ON COLI_COCM_CODE = COCM_CODE $where";
            
            $this->_db->setQuery($query);
            $this->_total = $this->_db->loadResult();
        }
        
        return $this->_total;
    } 
    
    function getPagination(){
        // Load the content if it doesn't already exist
        if (empty($this->_pagination)) {
            jimport('joomla.html.pagination');
            $this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit') );
        }
        return $this->_pagination;
    }
    
    //Get list COCH
    function getAllCoch($list = null){

	   $qr = "SELECT COCH_CODE,COCH_NAME,COCH_KANA,COCH_SEX FROM coch";
       // search
       if(!empty($list['filter_search'])){
            $where = " WHERE COCH_CODE = '$list[filter_search]' || COCH_NAME LIKE '%$list[filter_search]%' || COCH_KANA LIKE '%$list[filter_search]%'";
       }else{
            $where = null;
       }
       
       // get total number record
       //$this->_total = $this->getTotalTerm($where);
       
       $qr .= $where;
       
       // order by
       $qr .= " ORDER BY COCH_CODE ASC"; 
       
       // pagination
       //$qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');
          
       
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
	}
    
    //Get list OCBA
    function getAllOcba($list = null){

	   $qr = "SELECT OCBA_CODE,OCBA_NAME,OCBA_KANA FROM ocba WHERE OCBA_FLAG = 1";
       // search
       if(!empty($list['filter_search'])){
            $where = " AND OCBA_CODE = '$list[filter_search]' || OCBA_NAME LIKE '%$list[filter_search]%' || OCBA_KANA LIKE '%$list[filter_search]%'";
       }else{
            $where = null;
       }
       
       // get total number record
       //$this->_total = $this->getTotalTerm($where);
       
       $qr .= $where;
       
       // order by
       $qr .= " ORDER BY OCBA_CODE ASC"; 
       
       // pagination
       //$qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');
          
       
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
	}
    //Get list COCM
    function getAllCocm(){

	   $qr = "SELECT COCM_CODE,COCM_NAME FROM cocm WHERE COCM_FLAG = 1";
       // order by
       $qr .= " ORDER BY COCM_CODE ASC"; 
       
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
	}
    //Get COLI_CODE max
    function getMaxCOLI_CODE($where = null){
        // Load the content if it doesn't already exist
        $query = "SELECT MAX(COLI_CODE) FROM coli";
            
        $this->_db->setQuery($query);
        $this->_max = $this->_db->loadResult();
        
        return $this->_max;
    }
    //Get name center club
    function getNameCenterClub($id){
        // Load the content if it doesn't already exist
        $query = "SELECT OCBA_NAME FROM ocba WHERE OCBA_CODE=$id";
            
        $this->_db->setQuery($query);
        $result = $this->_db->loadResult();
        
        return $result;
    }
}
